package com.ipan.poi.excel.util;

import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;

import javax.persistence.Column;
import javax.persistence.Table;

import com.ipan.poi.PoiConfig;
import com.ipan.poi.excel.config.XlsEntity;
import com.ipan.poi.excel.config.XlsProperty;
import com.ipan.poi.excel.service.MatchType;
import com.ipan.poi.orm.NamingStrategy;
import com.ipan.poi.utils.PoiBeanHelper;
import com.ipan.poi.utils.PoiStringHelper;

/**
 * SQL语句生成工具
 * 
 * @author iPan
 * @version 2013-9-20
 */
public class SqlUtils {
	
	/** 命名策略 */
	private static NamingStrategy NAMING_STRATEGY = PoiConfig.getInstance().createNamingStrategy();
	
	// 插入记录SQL（导入）
	private static final String INSERT_SQL = "insert into {table} ({column}) values({values})";
	// 更新记录SQL（导入）
	private static final String UPDATE_SQL = "update {table} set {updateColumn} where {validCond}";
	// 验证查询SQL（导入）
	private static final String SELECT_SQL = "select * from {table} where {validCond}";
	// 条件查询SQL（导出）
	private static final String SEARCH_SQL = "select * from {table} where {searchCond}";
	
	public static Object[] createSelectParams(XlsEntity defEntity, Object entity) {
		List<XlsProperty> list = defEntity.getValidProperty();
		if (list == null || list.size() < 1) {
			return null;
		}
		Object[] param = new Object[list.size()];
		for (int i=0; i<param.length; ++i) {
			String name = list.get(i).getName();
			param[i] = PoiBeanHelper.getBeanValue(entity, name);
		}
		return param;
	}
	
	public static Object[] createInsertParams(XlsEntity defEntity, Object entity) {
		List<XlsProperty> list = defEntity.getEnabledProperty();
		if (list == null || list.size() < 1) {
			return null;
		}
		Object[] param = new Object[list.size()];
		for (int i=0; i<param.length; ++i) {
			String name = list.get(i).getName();
			param[i] = PoiBeanHelper.getBeanValue(entity, name);
		}
		return param;
	}
	
	public static Object[] createUpdateParams(XlsEntity defEntity, Object entity) {
		List<XlsProperty> unValidList = defEntity.getUnValidProperty();
		List<XlsProperty> validList = defEntity.getValidProperty();
		if (validList == null || unValidList == null || validList.size() < 1 || unValidList.size() < 1) {
			return null;
		}
		Object[] param = new Object[unValidList.size() + validList.size()];
		int i = 0;
		for (int len=unValidList.size();i<len; ++i) {
			String name = unValidList.get(i).getName();
			param[i] = PoiBeanHelper.getBeanValue(entity, name);
		}
		for (int curIndex=i; i<param.length; ++i) {
			String name = validList.get(i - curIndex).getName();
			param[i] = PoiBeanHelper.getBeanValue(entity, name);
		}
		return param;
	}
	
	public static Object[] createSearchParams(XlsEntity defEntity, Object entity) {
		List<XlsProperty> list = defEntity.getSearchProperty();
		if (list == null || list.size() < 1) {
			return null;
		}
		List<Object> param = new ArrayList<Object>();
		for (int i=0, len=list.size(); i<len; ++i) {
			String name = list.get(i).getName();
			Object value = PoiBeanHelper.getBeanValue(entity, name);
			if (value == null || (value instanceof CharSequence && ((CharSequence)value).length() < 1)) {
				continue;
			}
			param.add(value);
		}
		return (param.size() == 0) ? null : param.toArray();
	}

	/**
	 * 创建验证查询SQL
	 * 验证字段必须要有，否则返回null；
	 */
	public static String createSelectSql(XlsEntity defEntity, Object entity) {
		if (defEntity == null || defEntity.getValidProperty() == null || defEntity.getValidProperty().size() < 1 || entity == null) {
			return null;
		}
		String sql = SELECT_SQL;
		sql = convertTable(sql, entity.getClass());
		sql = convertValidCond(sql, defEntity, entity);
		return sql;
	}
	
	/**
	 * 创建插入SQL
	 */
	public static String createInsertSql(XlsEntity defEntity, Object entity) {
		if (defEntity == null || entity == null) {
			return null;
		}
		String sql = INSERT_SQL;
		sql = convertTable(sql, entity.getClass());
		sql = convertColumn(sql, defEntity, entity);
		sql = convertValues(sql, defEntity);
		return sql;
	}

	/**
	 * 创建更新SQL
	 * 验证字段必须要有，否则返回null；
	 */
	public static String createUpdateSql(XlsEntity defEntity, Object entity) {
		if (defEntity == null || defEntity.getValidProperty() == null || defEntity.getValidProperty().size() < 1 || entity == null) {
			return null;
		}
		String sql = UPDATE_SQL;
		sql = convertTable(sql, entity.getClass());
		sql = convertUpdateColumn(sql, defEntity, entity);
		sql = convertValidCond(sql, defEntity, entity);
		return sql;
	}
	
	/**
	 * 创建条件查询SQL
	 * 条件查询字段配置列表内，若有参数有值则加入查询条件，否则忽略；
	 */
	public static String createSearchSql(XlsEntity defEntity, Object entity) {
		if (defEntity == null || entity == null) {
			return null;
		}
		String sql = SEARCH_SQL;
		sql = convertTable(sql, entity.getClass());
		sql = convertSearchCond(sql, defEntity, entity);
		return sql;
	}

	/**
	 * 获取实体类表名；
	 */
	public static String getTableName(Class<?> cl) {
		String name = cl.getName();
		Table entityAnn = cl.getAnnotation(Table.class);
		if (entityAnn != null && PoiStringHelper.isNotBlank(entityAnn.name())) {
			name = entityAnn.name().toLowerCase();
		}
		return NAMING_STRATEGY.classToTableName(name);
	}

	/**
	 * 获取实体类字段名；
	 */
	public static String getColumnName(Field field) {
		String name = field.getName();
		Column column = field.getAnnotation(Column.class);
		if (column != null && PoiStringHelper.isNotBlank(column.name())) {
			name = column.name().toLowerCase();
		}
		return NAMING_STRATEGY.propertyToColumnName(name);
	}

	private static String convertUpdateColumn(String sql, XlsEntity defEntity, Object entity) {
		List<XlsProperty> properties = defEntity.getUnValidProperty();
		StringBuilder buf = new StringBuilder();
		for (int i = 0, len = properties.size(); i < len; ++i) {
			if (i > 0) {
				buf.append(", ");
			}
			String name = properties.get(i).getName();
			Field field = null;
			try {
				field = entity.getClass().getDeclaredField(name);
			} catch (Exception e) {
				throw new RuntimeException("找不到属性名：" + name + "！");
			}
			String columnName = getColumnName(field);
			buf.append(columnName).append(" = ?");
		}
		return sql.replace("{updateColumn}", buf.toString());
	}

	private static String convertValues(String sql, XlsEntity defEntity) {
		StringBuilder buf = new StringBuilder();
		for (int i = 0, count=defEntity.getEnabledProperty().size(); i < count; ++i) {
			if (i > 0) {
				buf.append(", ");
			}
			buf.append("?");
		}
		return sql.replace("{values}", buf.toString());
	}

	private static String convertColumn(String sql, XlsEntity defEntity, Object entity) {
		List<XlsProperty> properties = defEntity.getEnabledProperty();
		StringBuilder buf = new StringBuilder();
		for (int i = 0, len = properties.size(); i < len; ++i) {
			if (i > 0) {
				buf.append(", ");
			}
			String name = properties.get(i).getName();
			Field field = null;
			try {
				field = entity.getClass().getDeclaredField(name);
			} catch (Exception e) {
				throw new RuntimeException("找不到属性名：" + name + "！");
			}
			String columnName = getColumnName(field);
			buf.append(columnName);
		}
		return sql.replace("{column}", buf.toString());
	}

	private static String convertValidCond(String sql, XlsEntity defEntity, Object entity) {
		List<XlsProperty> validColumnList = defEntity.getValidProperty();
		StringBuilder buf = new StringBuilder();
		for (int i = 0, len = validColumnList.size(); i < len; ++i) {
			if (i > 0) {
				buf.append(" and ");
			}
			String name = validColumnList.get(i).getName();
			Field field = null;
			try {
				field = entity.getClass().getDeclaredField(name);
			} catch (Exception e) {
				throw new RuntimeException("找不到属性名：" + name + "！");
			}
			String columnName = getColumnName(field);
			buf.append(columnName).append(" = ?");
		}
		return sql.replace("{validCond}", buf.toString());
	}

	private static String convertTable(String sql, Class<?> cl) {
		String table = getTableName(cl);
		return sql.replace("{table}", table);
	}
	
	private static String convertSearchCond(String sql, XlsEntity defEntity, Object entity) {
		List<XlsProperty> searchColumnList = defEntity.getSearchProperty();
		StringBuilder buf = new StringBuilder();
		for (int i = 0, len = searchColumnList.size(); i < len; ++i) {
			String name = searchColumnList.get(i).getName();
			String search = searchColumnList.get(i).getSearch().toUpperCase();
			// 如果查询条件的字段为空则忽略
			Object value = PoiBeanHelper.getBeanValue(entity, name);
			if (value == null || (value instanceof CharSequence && ((CharSequence)value).length() < 1)) {
				searchColumnList.remove(i);
				i--;
				len--;
				continue;
			}
			
			if (i > 0) {
				buf.append(" and ");
			}
			Field field = null;
			try {
				field = entity.getClass().getDeclaredField(name);
			} catch (Exception e) {
				throw new RuntimeException("找不到属性名：" + name + "！");
			}
			String columnName = getColumnName(field);
			buf.append(columnName).append(" ").append(MatchType.valueOf(search).getTag()).append(" ?");
		}
		sql = sql.replace("{searchCond}", buf.toString()).trim();
		if (sql.endsWith("where")) {
			sql = sql.substring(0, sql.lastIndexOf("where") - 1);
		}
		return sql;
	}
	
}
